How to select first row in each GROUP BY group?

Question

Ashutosh Kumar Verma · Answer

In SQL Server, you can use the ROW_NUMBER() function with a Common Table Expression (CTE) or subquery to achieve this,
Suppose we have two SQL table,and 
Departments Table
CREATE TABLE Departments
(
ID INT NOT NULL PRIMARY KEY IDENTITY(100, 1),
Name NVARCHAR(50) NOT NULL,
Location NVARCHAR(50)
)
Employees Table
CREATE TABLE Employees
(
EmpId INT PRIMARY KEY NOT NULL IDENTITY(100, 1),
EmpName NVARCHAR(50) NOT NULL,
Gender NVARCHAR(20),
Salary DECIMAL(10, 2),
DepartmentId INT FOREIGN KEY REFERENCES Departments(ID)
)
Let's see the processed,
Using SQL Subquery
By using SQL, server Subquery you can easily select the first records from each grouped items.
Example-
SELECT EmpId, EmpName, Gender, Salary, Department  FROM (
	SELECT E.EmpId, E.EmpName, E.Gender, E.Salary,D.Name AS Department, 
	ROW_NUMBER() OVER(PARTITION BY D.Name ORDER BY D.Name) AS RowNum 
	FROM Employees E INNER JOIN Departments D ON D.ID = E.DepartmentId
) AS GroupedData WHERE RowNum = 1
Output-
and 
and 
Using SQL CTEand 
You can also achieve the same result by using the SQL CTE (Common Table Expression)
Example-
USE MyCollegeDb
GO
WITH CTE AS (
SELECT E.EmpId, E.EmpName, E.Gender, E.Salary,D.Name AS Department, 
ROW_NUMBER() OVER(PARTITION BY D.Name ORDER BY D.Name) AS RowNum 
FROM Employees E INNER JOIN Departments D ON D.ID = E.DepartmentId
)
SELECT EmpId, EmpName, Gender, Salary, Department FROM CTE WHERE RowNum =1
Output-
and 
In the example above-
ROW_NUMBER() This function assigns a unique sequence of integers to each row in the partition of the result set defined by the
PARTITION BY clause. The ORDER BY clause in ROW_NUMBER() determines the order in which the numbers are assigned.
PARTITION BY Specifies how to divide the result set into partitions. Rows with the same values ​​in YourGroupColumn will belong to the same partition.
ORDER BY Defines the order of the rows in each partition. SomeColumn is often used here to refer to the first row.
If you apply condition RowNum = 1, you are effectively selecting only the first row in each group defined by
D.Name column.
and 
Also, Read: 
How to concatenate text from multiple rows into a single text string in SQL Server
and

forum

How to select first row in each GROUP BY group?

Sandra Emily

Can you answer this question?

1 Answers

Liked By